# _*_ coding:utf-8 _*_
# @Time  : 2023.02.21
# @Author: zizlee
from fastapi import APIRouter, Header, Query
from hutool import security
from v1_all_api.all_response import AllResponse
from db import FAConnection
from v1_all_api.all_utils import datetime_utils
from global_constant import USERS

from .accompany import accompany_api
from .lecture import lecture_api
from .investment_plan import investment_plan_api
from .quota_coin import quota_coin_api
from .hedging import hedging_api
from .customer_online import customer_online_api
from .analysis_report import analysis_report_api
from .ppt_courseware import ppt_courseware_api
from .data_need import data_need_api
from .wx_group import wx_group_api
from .wind_remote import wind_remote_api
from .ruizhi import ruizhi_api
from .zhenduan import zhenduan_api
from .shouquan import shouquan_api


prod_service_router = APIRouter()

prod_service_router.include_router(accompany_api, prefix='/accompany')
prod_service_router.include_router(lecture_api, prefix='/lecture')
prod_service_router.include_router(investment_plan_api, prefix='/investment')
prod_service_router.include_router(hedging_api, prefix='/hedging')
prod_service_router.include_router(quota_coin_api, prefix='/quota')
prod_service_router.include_router(customer_online_api, prefix='/customerOnline')
prod_service_router.include_router(analysis_report_api, prefix='/analysisReport')
prod_service_router.include_router(ppt_courseware_api, prefix='/pptCourseware')
prod_service_router.include_router(data_need_api, prefix='/dataNeed')
prod_service_router.include_router(wx_group_api, prefix='/wxGroup')
prod_service_router.include_router(wind_remote_api, prefix='/windRemote')
prod_service_router.include_router(ruizhi_api, prefix='/ruizhi')
prod_service_router.include_router(zhenduan_api, prefix='/zhenduan')
prod_service_router.include_router(shouquan_api, prefix='/shouquan')


# 查询所有类别待处理的任务
@prod_service_router.get('/')
def query_handle_tasks(ruizy_token: str = Header(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    is_manager = security.operator_is_manager(person)
    states = {
        -1: '拒绝',
        0: '待分配',
        1: '待完成',
        2: '完成'
    }

    # 查询演讲培训未处理的任务
    lecture_sql = """
        SELECT '演讲培训' AS module_name,a.id,a.create_time,a.topic,a.lecture_time,a.state,u.admin_name
        FROM prse_lecture As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 查询陪同走访未处理的任务
    accompany_sql = """
        SELECT '陪同走访' AS module_name,a.id,a.create_time,a.visit_to,a.visit_time,a.state,u.admin_name
        FROM prse_accompany_visit As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 投资计划
    investment_plan_sql = """
        SELECT '投资计划' AS module_name,a.id,a.create_time,a.variety,a.finish_time,a.state,u.admin_name
        FROM prse_investment_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 套保方案
    hedge_plan_sql = """
        SELECT '套保方案' AS module_name,a.id,a.create_time,a.company_name,a.variety,a.finish_time,a.state,u.admin_name
        FROM prse_hedging_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 客户微信群服务
    customer_online_sql = """
        SELECT '客户微信群服务' AS module_name,a.id,a.create_time,a.company_name,a.variety,a.finish_time,a.state,u.admin_name
        FROM prse_customer_online As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 分析报告
    analysis_report_sql = """
        SELECT '分析报告' AS module_name,a.id,a.create_time,a.company_name,a.title,a.finish_time,a.state,u.admin_name
        FROM prse_analysis_report As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # PPT课件
    ppt_courseware_sql = """
        SELECT 'PPT课件' AS module_name,a.id,a.create_time,a.company_name,a.title,a.finish_time,a.state,u.admin_name
        FROM prse_ppt As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 材料需求
    data_need_sql = """
        SELECT '材料需求' AS module_name,a.id,a.create_time,a.title,a.data_type,a.finish_time,a.state,u.admin_name
        FROM prse_data_need As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 微信群
    wx_group_sql = """
        SELECT '微信沟通平台' AS module_name,a.id,a.create_time,a.wx_name,a.wx_code,a.expire_time,a.state,u.admin_name
        FROM prse_wx_group As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # wind远程
    wind_remote_sql = """
        SELECT 'wind远程' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,u.admin_name
        FROM prse_wind_remote As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 瑞智申请
    ruizhi_sql = """
        SELECT '瑞智系统试用' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,u.admin_name
        FROM prse_ruizhi As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<2
    """
    # 诊断系统申请
    zhenduan_sql = """
        SELECT '期易通试用' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,u.admin_name
        FROM prse_zhenduan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.state<3
    """
    # 授权系统申请
    shouquan_sql = """
           SELECT '产品授权' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,u.admin_name
           FROM prse_shouquan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
           WHERE a.state>=0 AND a.state<3
       """

    if not is_manager:
        lecture_handle_router = 'DeptWorkCoordinationLectureApplyToMe'
        lecture_sql += ' AND worker_id=%s;' % person['uid']
        accompany_handle_router = 'DeptWorkCoordinationAccompanyApplyToMe'
        accompany_sql += ' AND worker_id=%s;' % person['uid']
        investment_handle_router = 'DeptWorkCoordinationInvestmentApplyToMe'
        investment_plan_sql += ' AND worker_id=%s;' % person['uid']
        hedge_plan_sql += ' AND worker_id=%s;' % person['uid']
        hedge_plan_router = 'DeptWorkCoordinationHegePlanApplyToMe'
        customer_online_sql += ' AND worker_id=%s;' % person['uid']
        customer_online_router = 'DeptWorkCoordinationCustomerOnlineApplyToMe'
        analysis_report_sql += ' AND worker_id=%s;' % person['uid']
        analysis_report_router = 'DeptWorkCoordinationAnalysisReportApplyToMe'
        ppt_courseware_sql += ' AND worker_id=%s;' % person['uid']
        ppt_courseware_router = 'DeptWorkCoordinationPPTCoursewareApplyToMe'
        data_need_sql += ' AND worker_id=%s;' % person['uid']
        data_need_router = 'DeptWorkCoordinationDataNeedApplyToMe'
        wx_group_sql += ' AND worker_id=%s;' % person['uid']
        wx_group_router = 'DeptWorkCoordinationWxGroupApplyToMe'
        wind_remote_sql += ' AND worker_id=%s;' % person['uid']
        wind_remote_router = 'DeptWorkCoordinationWindRemoteApplyToMe'
        ruizhi_sql += ' AND worker_id=%s;' % person['uid']
        ruizhi_router = 'DeptWorkCoordinationRuizhiApplyToMe'
        zhenduan_sql += ' AND worker_id=%s;' % person['uid']
        zhenduan_router = 'DeptWorkCoordinationZhenduanApplyToMe'
        shouquan_sql += ' AND worker_id=%s;' % person['uid']
        shouquan_router = 'DeptWorkCoordinationShouquanApplyToMe'
    else:
        lecture_handle_router = 'DeptWorkCoordinationLectureApplyMag'
        lecture_sql += ';'
        accompany_handle_router = 'DeptWorkCoordinationAccompanyApplyMag'
        accompany_sql += ';'
        investment_handle_router = 'DeptWorkCoordinationInvestmentApplyMag'
        investment_plan_sql += ';'
        hedge_plan_sql += ';'
        hedge_plan_router = 'DeptWorkCoordinationHedgePlanApplyMag'
        customer_online_sql += ';'
        customer_online_router = 'DeptWorkCoordinationCustomerOnlineApplyMag'
        analysis_report_sql += ';'
        analysis_report_router = 'DeptWorkCoordinationAnalysisReportApplyMag'
        ppt_courseware_sql += ';'
        ppt_courseware_router = 'DeptWorkCoordinationPPTCoursewareApplyMag'
        data_need_sql += ';'
        data_need_router = 'DeptWorkCoordinationDataNeedApplyMag'
        wx_group_sql += ';'
        wx_group_router = 'DeptWorkCoordinationWxGroupApplyMag'
        wind_remote_sql += ';'
        wind_remote_router = 'DeptWorkCoordinationWindRemoteApplyMag'
        ruizhi_sql += ';'
        ruizhi_router = 'DeptWorkCoordinationRuizhiApplyMag'
        zhenduan_sql += ';'
        zhenduan_router = 'DeptWorkCoordinationZhenduanApplyMag'
        shouquan_sql += ';'
        shouquan_router = 'DeptWorkCoordinationShouquanApplyMag'

    with FAConnection() as cursor:
        # 演讲培训
        cursor.execute(lecture_sql)
        lecture_list = list(cursor.fetchall())
        # 协同服务
        cursor.execute(accompany_sql)
        accompany_visit_list = list(cursor.fetchall())
        # 投资计划
        cursor.execute(investment_plan_sql)
        investment_plan_list = list(cursor.fetchall())
        # 投资方案
        cursor.execute(hedge_plan_sql)
        hedge_plan_list = list(cursor.fetchall())
        # 客户微信群服务
        cursor.execute(customer_online_sql)
        online_list = list(cursor.fetchall())
        # 分析报告
        cursor.execute(analysis_report_sql)
        analysis_report_list = list(cursor.fetchall())
        # ppt课件
        cursor.execute(ppt_courseware_sql)
        ppt_courseware_list = list(cursor.fetchall())
        # 材料需求
        cursor.execute(data_need_sql)
        data_need_list = list(cursor.fetchall())
        # 微信沟通平台
        cursor.execute(wx_group_sql)
        wx_group_list = list(cursor.fetchall())
        # wind远程
        cursor.execute(wind_remote_sql)
        wind_remote_list = list(cursor.fetchall())
        # 瑞智试用
        cursor.execute(ruizhi_sql)
        ruizhi_list = list(cursor.fetchall())
        # 期易通试用
        cursor.execute(zhenduan_sql)
        zhenduan_list = list(cursor.fetchall())
        # 产品授权
        cursor.execute(shouquan_sql)
        shouquan_list = list(cursor.fetchall())

    data_list = []
    badge_list = []
    # 1演讲培训
    for row in lecture_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': lecture_handle_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['topic'],
            'service_time': row['lecture_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text':  states.get(row['state'], '无')
        })
    # 2陪同走访
    for row in accompany_visit_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': accompany_handle_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['visit_to'],
            'service_time': row['visit_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 3投资计划
    for row in investment_plan_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': accompany_handle_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['variety'] + '投资计划',
            'finish_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 4套保方案
    for row in hedge_plan_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': hedge_plan_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['company_name'] + row['variety'] + '套保方案',
            'finish_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 5客户微信群服务
    for row in online_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': hedge_plan_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['company_name'] + '线上服务',
            'finish_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 6分析报告
    for row in analysis_report_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': analysis_report_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['title'],
            'finish_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 7PPT课件
    for row in ppt_courseware_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': analysis_report_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['title'],
            'finish_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 8材料需求
    for row in data_need_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': analysis_report_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['title'],
            'finish_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'state_text': states.get(row['state'], '无')
        })
    # 9微信沟通平台
    for row in wx_group_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': wx_group_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['wx_name'],
            'finish_time': None,
            'state_text': states.get(row['state'], '无')
        })
    # 10wind远程
    for row in wind_remote_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': wind_remote_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + 'wind远程',
            'finish_time': None,
            'state_text': states.get(row['state'], '无')
        })

    # 11瑞智系统试用
    for row in ruizhi_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': ruizhi_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + '瑞智试用',
            'finish_time': None,
            'state_text': states.get(row['state'], '无')
        })
    # 12期易通试用
    for row in zhenduan_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': zhenduan_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + '期易通试用',
            'finish_time': None,
            'state_text': {
                -1: '拒绝',
                0: '待确认',
                1: '待审批',
                2: '待生成',
                3: '已完成'
            }.get(row['state'], '无')
        })
    # 13产品授权
    for row in shouquan_list:
        data_list.append({
            'module_name': row['module_name'],
            'router_name': shouquan_router,
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + '产品授权',
            'finish_time': None,
            'state_text': {
                -1: '拒绝',
                0: '待确认',
                1: '待审批',
                2: '待生成',
                3: '已完成'
            }.get(row['state'], '无')
        })
    if len(lecture_list) > 0:
        badge_list.append({
            'module_name': '演讲培训',
            'key': 'lecture',
            'router_name': lecture_handle_router,
            'row_count': len(lecture_list)
        })
    if len(accompany_visit_list) > 0:
        badge_list.append({
            'module_name': '陪同走访',
            'key': 'accompany',
            'router_name': accompany_handle_router,
            'row_count': len(accompany_visit_list)
        })
    if len(investment_plan_list) > 0:
        badge_list.append({
            'module_name': '投资计划',
            'key': 'investment',
            'router_name': investment_handle_router,
            'row_count': len(investment_plan_list)
        })
    if len(hedge_plan_list) > 0:
        badge_list.append({
            'module_name': '套保方案',
            'key': 'hedge_plan',
            'router_name': hedge_plan_router,
            'row_count': len(hedge_plan_list)
        })
    if len(online_list) > 0:
        badge_list.append({
            'module_name': '客户微信群服务',
            'key': 'customer_online',
            'router_name': customer_online_router,
            'row_count': len(online_list)
        })
    if len(analysis_report_list) > 0:
        badge_list.append({
            'module_name': '分析报告',
            'key': 'analysis_report',
            'router_name': analysis_report_router,
            'row_count': len(analysis_report_list)
        })
    if len(ppt_courseware_list) > 0:
        badge_list.append({
            'module_name': 'PPT课件',
            'key': 'ppt_courseware',
            'router_name': ppt_courseware_router,
            'row_count': len(ppt_courseware_list)
        })
    if len(data_need_list) > 0:
        badge_list.append({
            'module_name': '材料需求',
            'key': 'data_need',
            'router_name': data_need_router,
            'row_count': len(data_need_list)
        })
    if len(wx_group_list) > 0:
        badge_list.append({
            'module_name': '微信沟通平台',
            'key': 'wx_group',
            'router_name': wx_group_router,
            'row_count': len(wx_group_list)
        })
    if len(wind_remote_list) > 0:
        badge_list.append({
            'module_name': 'wind远程',
            'key': 'wind_remote',
            'router_name': wind_remote_router,
            'row_count': len(wind_remote_list)
        })
    if len(ruizhi_list) > 0:
        badge_list.append({
            'module_name': '瑞智系统试用',
            'key': 'ruizhi',
            'router_name': ruizhi_router,
            'row_count': len(ruizhi_list)
        })
    if len(zhenduan_list) > 0:
        badge_list.append({
            'module_name': '交易诊断',
            'key': 'zhenduan',
            'router_name': zhenduan_router,
            'row_count': len(zhenduan_list)
        })
    if len(shouquan_list) > 0:
        badge_list.append({
            'module_name': '产品授权',
            'key': 'shouquan',
            'router_name': shouquan_router,
            'row_count': len(shouquan_list)
        })
    response_data = {
        'data': data_list,
        'badge': badge_list
    }
    return AllResponse.operate_successfully(data=response_data)


# 统计各模块的数据
@prod_service_router.get('/statistics/')
def statistic_prod_service(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    start_date = datetime_utils.auth_datetime_string(start, f='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='参数错误!')
    states = {
        -1: '拒绝',
        0: '待分配',
        1: '待完成',
        2: '完成'
    }
    start_date = start_date + ' 00:00:00'
    end_date = end_date + ' 23:59:59'
    # 演讲培训
    lecture_sql = """
        SELECT '演讲培训' AS module_name,a.id,a.create_time,a.topic,a.lecture_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_lecture As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 陪同走访
    accompany_sql = """
        SELECT '陪同走访' AS module_name,a.id,a.create_time,a.visit_to,a.visit_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_accompany_visit As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 投资计划
    investment_plan_sql = """
        SELECT '投资计划' AS module_name,a.id,a.create_time,a.variety,a.finish_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_investment_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 套保方案
    hedge_plan_sql = """
        SELECT '套保方案' AS module_name,a.id,a.create_time,a.company_name,a.variety,a.finish_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_hedging_plan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 客户微信群服务
    customer_online_sql = """
        SELECT '客户微信群服务' AS module_name,a.id,a.create_time,a.company_name,a.variety,a.finish_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_customer_online As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 分析报告
    analysis_report_sql = """
        SELECT '分析报告' AS module_name,a.id,a.create_time,a.company_name,a.title,a.finish_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_analysis_report As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # PPT课件
    ppt_courseware_sql = """
        SELECT 'PPT课件' AS module_name,a.id,a.create_time,a.company_name,a.title,a.finish_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_ppt As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 材料需求
    data_need_sql = """
        SELECT '材料需求' AS module_name,a.id,a.create_time,a.title,a.data_type,a.finish_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_data_need As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 微信群
    wx_group_sql = """
        SELECT '微信沟通平台' AS module_name,a.id,a.create_time,a.wx_name,a.wx_code,a.expire_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_wx_group As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # wind远程
    wind_remote_sql = """
        SELECT 'wind远程' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_wind_remote As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 瑞智申请
    ruizhi_sql = """
        SELECT '瑞智系统试用' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_ruizhi As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 诊断系统申请
    zhenduan_sql = """
        SELECT '期易通试用' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,a.quota,a.worker_id,u.admin_name
        FROM prse_zhenduan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
        WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
    """.format(start_date, end_date)
    # 产品授权申请
    shouquan_sql = """
            SELECT '产品授权' AS module_name,a.id,a.create_time,a.use_name,a.expire_time,a.state,a.quota,a.worker_id,u.admin_name
            FROM prse_shouquan As a INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.state>=0 AND a.create_time>='{0}' AND a.create_time<='{1}';
        """.format(start_date, end_date)
    with FAConnection() as cursor:
        # 演讲培训
        cursor.execute(lecture_sql)
        lecture_list = list(cursor.fetchall())
        # 协同服务
        cursor.execute(accompany_sql)
        accompany_visit_list = list(cursor.fetchall())
        # 投资计划
        cursor.execute(investment_plan_sql)
        investment_plan_list = list(cursor.fetchall())
        # 投资方案
        cursor.execute(hedge_plan_sql)
        hedge_plan_list = list(cursor.fetchall())
        # 客户微信群服务
        cursor.execute(customer_online_sql)
        online_list = list(cursor.fetchall())
        # 分析报告
        cursor.execute(analysis_report_sql)
        analysis_report_list = list(cursor.fetchall())
        # ppt课件
        cursor.execute(ppt_courseware_sql)
        ppt_courseware_list = list(cursor.fetchall())
        # 材料需求
        cursor.execute(data_need_sql)
        data_need_list = list(cursor.fetchall())
        # 微信沟通平台
        cursor.execute(wx_group_sql)
        wx_group_list = list(cursor.fetchall())
        # wind远程
        cursor.execute(wind_remote_sql)
        wind_remote_list = list(cursor.fetchall())
        # 瑞智试用
        cursor.execute(ruizhi_sql)
        ruizhi_list = list(cursor.fetchall())
        # 期易通试用
        cursor.execute(zhenduan_sql)
        zhenduan_list = list(cursor.fetchall())
        # 产品授权
        cursor.execute(shouquan_sql)
        shouquan_list = list(cursor.fetchall())

    # 演讲培训
    lecture_groups = {}
    lecture_data = []
    for row in lecture_list:
        lecture_groups.setdefault(row['state'], []).append(row)
        lecture_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['topic'],
            'service_time': row['lecture_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 陪同走访
    accompany_visit_groups = {}
    accompany_visit_data = []
    for row in accompany_visit_list:
        accompany_visit_groups.setdefault(row['state'], []).append(row)
        accompany_visit_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['visit_to'],
            'service_time': row['visit_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 投资计划
    investment_plan_groups = {}
    investment_plan_data = []
    for row in investment_plan_list:
        investment_plan_groups.setdefault(row['state'], []).append(row)
        investment_plan_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['variety'] + '投资计划',
            'service_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 4套保方案
    hedge_plan_groups = {}
    hedge_plan_data = []
    for row in hedge_plan_list:
        hedge_plan_groups.setdefault(row['state'], []).append(row)
        hedge_plan_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['company_name'] + row['variety'] + '套保方案',
            'service_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 5客户微信群服务
    online_groups = {}
    online_data = []
    for row in online_list:
        online_groups.setdefault(row['state'], []).append(row)
        online_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['company_name'] + '线上服务',
            'service_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 6分析报告
    analysis_report_groups = {}
    analysis_report_data = []
    for row in analysis_report_list:
        analysis_report_groups.setdefault(row['state'], []).append(row)
        analysis_report_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['title'],
            'service_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 7PPT课件
    ppt_courseware_groups = {}
    ppt_courseware_data = []
    for row in ppt_courseware_list:
        ppt_courseware_groups.setdefault(row['state'], []).append(row)
        ppt_courseware_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['title'],
            'service_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 8材料需求
    data_need_groups = {}
    data_need_data = []
    for row in data_need_list:
        data_need_groups.setdefault(row['state'], []).append(row)
        data_need_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['title'],
            'service_time': row['finish_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 9微信沟通平台
    wx_group_groups = {}
    wx_group_data = []
    for row in wx_group_list:
        wx_group_groups.setdefault(row['state'], []).append(row)
        wx_group_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['wx_name'],
            'service_time': None,
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 10wind远程
    wind_remote_groups = {}
    wind_remote_data = []
    for row in wind_remote_list:
        wind_remote_groups.setdefault(row['state'], []).append(row)
        wind_remote_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + 'wind远程',
            'service_time': None,
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 11瑞智系统试用
    ruizhi_groups = {}
    ruizhi_data = []
    for row in ruizhi_list:
        ruizhi_groups.setdefault(row['state'], []).append(row)
        ruizhi_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + '瑞智试用',
            'service_time': None,
            'quota': row['quota'],
            'state': row['state'],
            'state_text': states.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 12期易通试用
    zhenduan_groups = {}
    zhenduan_data = []
    for row in zhenduan_list:
        zhenduan_groups.setdefault(row['state'], []).append(row)
        zhenduan_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + '瑞智试用',
            'service_time': None,
            'quota': row['quota'],
            'state': row['state'],
            'state_text': {
                -1: '拒绝',
                0: '待确认',
                1: '待审批',
                2: '待生成',
                3: '已完成'
            }.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })
    # 13产品授权申请
    shouquan_groups = {}
    shouquan_data = []
    for row in shouquan_list:
        shouquan_groups.setdefault(row['state'], []).append(row)
        shouquan_data.append({
            'module_name': row['module_name'],
            'applicant': row['admin_name'],
            'create_time': row['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'title': row['use_name'] + '授权申请',
            'service_time': None,
            'quota': row['quota'],
            'state': row['state'],
            'state_text': {
                -1: '拒绝',
                0: '待确认',
                1: '待审批',
                2: '待生成',
                3: '已完成'
            }.get(row['state'], '无'),
            'worker_id': row['worker_id'],
            'work_name': USERS.DEPARTMENT_NAMES.get(row['worker_id'])
        })

    statistics_data = [
        {
            'module_name': '演讲培训',
            'key': 'lecture',
            'total_count': len(lecture_data),
            'total_quota': sum([i['quota'] for i in lecture_data]),
            'running_count': len(lecture_groups.get(0, [])) + len(lecture_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in lecture_data if i['state'] != 2]),
            'finish_count': len(lecture_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in lecture_data if i['state'] == 2]),
            'data': lecture_data,
        },
        {
            'module_name': '陪同走访',
            'key': 'lecture',
            'total_count': len(accompany_visit_data),
            'total_quota': sum([i['quota'] for i in accompany_visit_data]),
            'running_count': len(accompany_visit_groups.get(0, [])) + len(accompany_visit_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in accompany_visit_data if i['state'] != 2]),
            'finish_count': len(accompany_visit_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in accompany_visit_data if i['state'] == 2]),
            'data': accompany_visit_data,
        },
        {
            'module_name': '投资计划',
            'key': 'lecture',
            'total_count': len(investment_plan_data),
            'total_quota': sum([i['quota'] for i in investment_plan_data]),
            'running_count': len(investment_plan_groups.get(0, [])) + len(investment_plan_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in investment_plan_data if i['state'] != 2]),
            'finish_count': len(investment_plan_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in investment_plan_data if i['state'] == 2]),
            'data': investment_plan_data,
        },
        {
            'module_name': '套保方案',
            'key': 'hedge',
            'total_count': len(hedge_plan_data),
            'total_quota': sum([i['quota'] for i in hedge_plan_data]),
            'running_count': len(hedge_plan_groups.get(0, [])) + len(hedge_plan_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in hedge_plan_data if i['state'] != 2]),
            'finish_count': len(hedge_plan_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in hedge_plan_data if i['state'] == 2]),
            'data': hedge_plan_data,
        },
        {
            'module_name': '客户微信群服务',
            'key': 'lecture',
            'total_count': len(online_data),
            'total_quota': sum([i['quota'] for i in online_data]),
            'running_count': len(online_groups.get(0, [])) + len(online_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in online_data if i['state'] != 2]),
            'finish_count': len(online_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in online_data if i['state'] == 2]),
            'data': online_data,
        },
        {
            'module_name': '分析报告',
            'key': 'lecture',
            'total_count': len(analysis_report_data),
            'total_quota': sum([i['quota'] for i in analysis_report_data]),
            'running_count': len(analysis_report_groups.get(0, [])) + len(analysis_report_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in analysis_report_data if i['state'] != 2]),
            'finish_count': len(analysis_report_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in analysis_report_data if i['state'] == 2]),
            'data': analysis_report_data,
        },
        {
            'module_name': 'PPT课件',
            'key': 'lecture',
            'total_count': len(ppt_courseware_data),
            'total_quota': sum([i['quota'] for i in ppt_courseware_data]),
            'running_count': len(ppt_courseware_groups.get(0, [])) + len(ppt_courseware_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in ppt_courseware_data if i['state'] != 2]),
            'finish_count': len(ppt_courseware_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in ppt_courseware_data if i['state'] == 2]),
            'data': ppt_courseware_data,
        },
        {
            'module_name': '材料需求',
            'key': 'lecture',
            'total_count': len(data_need_data),
            'total_quota': sum([i['quota'] for i in data_need_data]),
            'running_count': len(data_need_groups.get(0, [])) + len(data_need_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in data_need_data if i['state'] != 2]),
            'finish_count': len(data_need_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in data_need_data if i['state'] == 2]),
            'data': data_need_data,
        },
        {
            'module_name': '微信沟通平台',
            'key': 'lecture',
            'total_count': len(wx_group_data),
            'total_quota': sum([i['quota'] for i in wx_group_data]),
            'running_count': len(wx_group_groups.get(0, [])) + len(wx_group_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in wx_group_data if i['state'] != 2]),
            'finish_count': len(wx_group_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in wx_group_data if i['state'] == 2]),
            'data': wx_group_data,
        },
        {
            'module_name': 'wind远程',
            'key': 'lecture',
            'total_count': len(wind_remote_data),
            'total_quota': sum([i['quota'] for i in wind_remote_data]),
            'running_count': len(wind_remote_groups.get(0, [])) + len(wind_remote_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in wind_remote_data if i['state'] != 2]),
            'finish_count': len(wind_remote_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in wind_remote_data if i['state'] == 2]),
            'data': wind_remote_data,
        },
        {
            'module_name': '瑞智系统试用',
            'key': 'lecture',
            'total_count': len(ruizhi_data),
            'total_quota': sum([i['quota'] for i in ruizhi_data]),
            'running_count': len(ruizhi_groups.get(0, [])) + len(ruizhi_groups.get(1, [])),
            'running_quota': sum([i['quota'] for i in ruizhi_data if i['state'] != 2]),
            'finish_count': len(ruizhi_groups.get(2, [])),
            'finish_quota': sum([i['quota'] for i in ruizhi_data if i['state'] == 2]),
            'data': ruizhi_data,
        },
        {
            'module_name': '诊断系统',
            'key': 'lecture',
            'total_count': len(zhenduan_data),
            'total_quota': sum([i['quota'] for i in zhenduan_data]),
            'running_count': len(zhenduan_groups.get(0, [])) + len(zhenduan_groups.get(1, [])) + len(zhenduan_groups.get(2, [])),
            'running_quota': sum([i['quota'] for i in zhenduan_data if i['state'] != 3]),
            'finish_count': len(zhenduan_groups.get(3, [])),
            'finish_quota': sum([i['quota'] for i in zhenduan_data if i['state'] == 3]),
            'data': zhenduan_data,
        },
        {
            'module_name': '产品授权',
            'key': 'lecture',
            'total_count': len(shouquan_data),
            'total_quota': sum([i['quota'] for i in shouquan_data]),
            'running_count': len(shouquan_groups.get(0, [])) + len(shouquan_groups.get(1, [])) + len(
                shouquan_groups.get(2, [])),
            'running_quota': sum([i['quota'] for i in shouquan_data if i['state'] != 3]),
            'finish_count': len(shouquan_groups.get(3, [])),
            'finish_quota': sum([i['quota'] for i in shouquan_data if i['state'] == 3]),
            'data': shouquan_data,
        }
    ]
    # 将数据0 改为''
    for s in statistics_data:
        for k in ['total_count', 'total_quota', 'running_count', 'running_quota', 'finish_count', 'finish_quota']:
            if s[k] == 0:
                s[k] = ''
    res_data = {
        'statistics': statistics_data
    }

    return AllResponse.operate_successfully(data=res_data)
